package org.jeecgframework.web.util;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;

/**
 * XSSF and SAX (Event API)
 */
public class ExcelUtil extends DefaultHandler {
	private SharedStringsTable sst;
	private String lastContents;
	private boolean nextIsString;

	private int sheetIndex = -1;
	private List<String> rowlist = new ArrayList<String>();
	private int curRow = 0;
	private int curCol = 0;
	private List<Map<String, Object>> dataList = new ArrayList<Map<String, Object>>();
	private List<String> hadValCells = new ArrayList<String>();//excel 有内容的单元格坐标
	private static List<List<String>> hadValRows = new ArrayList<List<String>>();//excel 有内容的单元格 的集合
	private static String excelScope;//excel 有内容的最大单元格范围坐标
	private static String maxCol;//excel 有内容的最大单元格的列;
	private static List<String> colList = new ArrayList<String>();
	private static String lastCellIndex;
	static{
		//最大到ZZ
		for (int k = 0; k <= 26; k++) {
			int j = k >= 1 ? k : 0;
			for (int i = 1; i <= 26; i++) {
				if (j >= 1){
					colList.add(((char) (j + 64)) + "" + ((char) (i + 64)));
				}else{
					colList.add(((char) (i + 64)) + "");
				}
			}
		}
	}
	public static  XSSFWorkbook analyzeExcel2(InputStream is) throws Exception {
	    
		XSSFWorkbook hwb = null;   
		try {   
            hwb = new  XSSFWorkbook(is);
        } catch (IOException e) {   
            e.printStackTrace();   
        }
		return hwb;
	}
	
	
	//判断从Excel文件中解析出来数据的格式   
    private static String getCellValue2(XSSFCell xssfCell){  
        String value = null;  
        //简单的查检列类型   
        switch(xssfCell.getCellType())  
        {  
            case HSSFCell.CELL_TYPE_STRING://字符串   
                value = xssfCell.getRichStringCellValue().getString();  
                break;  
            case HSSFCell.CELL_TYPE_NUMERIC://数字   
                long dd = (long)xssfCell.getNumericCellValue();  
                value = dd+"";  
                break;  
            case HSSFCell.CELL_TYPE_BLANK:  
                value = "";  
                break;     
            case HSSFCell.CELL_TYPE_FORMULA:  
                value = String.valueOf(xssfCell.getCellFormula());  
                break;  
            case HSSFCell.CELL_TYPE_BOOLEAN://boolean型值   
                value = String.valueOf(xssfCell.getBooleanCellValue());  
                break;  
            case HSSFCell.CELL_TYPE_ERROR:  
                value = String.valueOf(xssfCell.getErrorCellValue());  
                break;
            default:  
                break;  
        }  
        return value;  
    }  
    
    public static String getCellValue(XSSFCell xssfCell){
    	String result = "";
    	if(null!=xssfCell){
    		result = getCellValue2(xssfCell);
    	}
    	return result.replace("\"", "").trim();
    }
	
    /**
     * 解析数据量较大excel
     * @param path  文件路径
     * @param sheetIndex 要解析的sheet页索引
     * @return map-key对应列号存储
     */
    public static List<Map<String, Object>> analyzeExcel(String path,String sheetIndex){
		ExcelUtil excel = new ExcelUtil();
		try {
			return excel.readOneSheet(path,sheetIndex);
		} catch (Exception e) {
			return null;
		}
	}

	/**
	 * 读取第一个工作簿的入口方法
	 * 
	 * @param path
	 */
	public List<Map<String, Object>> readOneSheet(String path,String sheetIndex) throws Exception {
		OPCPackage pkg = OPCPackage.open(path);
		XSSFReader r = new XSSFReader(pkg);
		SharedStringsTable sst = r.getSharedStringsTable();

		XMLReader parser = fetchSheetParser(sst);

		InputStream is = r.getSheet("rId"+sheetIndex);

		InputSource sheetSource = new InputSource(is);
		parser.parse(sheetSource);
		if(null != is){
			is.close();
		}
		return dataList;
	}

	/**
	 * 读取所有工作簿的入口方法
	 * 
	 * @param path
	 * @throws Exception
	 */
	public void process(String path) throws Exception {
		OPCPackage pkg = OPCPackage.open(path);
		XSSFReader r = new XSSFReader(pkg);
		SharedStringsTable sst = r.getSharedStringsTable();

		XMLReader parser = fetchSheetParser(sst);

		Iterator<InputStream> sheets = r.getSheetsData();
		while (sheets.hasNext()) {
			curRow = 0;
			sheetIndex++;
			InputStream sheet = sheets.next();
			InputSource sheetSource = new InputSource(sheet);
			parser.parse(sheetSource);
			sheet.close();
		}
	}

	/**
	 * 该方法自动被调用，每读一行调用一次，在方法中写自己的业务逻辑即可
	 * @param sheetIndex 工作簿序号
	 * @param curRow 处理到第几行
	 * @param rowDataList 当前数据行的数据集合
	 */
	private void optRow(int sheetIndex, int curRow, List<String> rowDataList) {
		try {
			Map<String, Object> rowDataMap = new HashMap<String, Object>();
			int colSize = colList.indexOf(maxCol) + 1;
			for (int j = 0,i = 0; j < colSize;j++) {
				if(!this.hadValCells.contains(colList.get(j))){
					rowDataMap.put(j + ""," ");
				}else if(i < rowDataList.size()){
					rowDataMap.put(j + "",rowDataList.get(i));
					i++;
				}
			}
			dataList.add(rowDataMap);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 读取结束后调用的方法
	 */
	public void endSheet(){
		
	}

	public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
		XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
		this.sst = sst;
		parser.setContentHandler(this);
		return parser;
	}

	public void startElement(String uri, String localName, String name,
			Attributes attributes) throws SAXException {
		switch (name) {
			case "c":
				// 如果下一个元素是 SST 的索引，则将nextIsString标记为true
				String cellType = attributes.getValue("t");
				if (cellType != null && cellType.equals("s")) {
					nextIsString = true;
				} else {
					nextIsString = false;
				}
				lastCellIndex = attributes.getValue("r").replaceAll("[^A-Z]", "");//因为在endElement中读取不到Attributes,所以在startElement中保存当前单元格
				break;
			case "dimension":
				excelScope = attributes.getValue("ref");
				maxCol = excelScope.split(":")[1].replaceAll("[^A-Z]", "");
				//System.out.println("内容范围 坐标" + attributes.getValue("ref"));//内容范围 坐标 A1:F5 (A1 到 F5)
				break;
			case "row":
				//System.out.println("当前行号:" + attributes.getValue("r"));//当前行号 从1开始
				break;
		}
		// 置空
		lastContents = "";
	}

	public void endElement(String uri, String localName, String name)
			throws SAXException {
		// 根据SST的索引值的到单元格的真正要存储的字符串
		// 这时characters()方法可能会被调用多次
		if (nextIsString) {
			try {
				int idx = Integer.parseInt(lastContents);
				lastContents = new XSSFRichTextString(sst.getEntryAt(idx))
						.toString();
			} catch (Exception e) {

			}
		}
		// v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
		// 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
		switch (name) {
			case "v":
				String value = lastContents.trim();
				value = value.equals("") ? " " : value;
				rowlist.add(curCol, value);
				hadValCells.add(lastCellIndex);//如果当前xml节点包含v 把当前节点的index 放入 集合中
				curCol++;
				break;
			case "row":
				optRow(sheetIndex, curRow, rowlist);
				rowlist.clear();
				curRow++;
				curCol = 0;
				hadValRows.add(hadValCells);
				hadValCells = new ArrayList<String>();
				break;
			case "sheetData":
				endSheet();
				break;
	
		}
	}

	public void characters(char[] ch, int start, int length)
			throws SAXException {
		// 得到单元格内容的值
		lastContents += new String(ch, start, length);
	}


	public static List<List<String>> getHadValRows() {
		return hadValRows;
	}

	public static String getExcelScope() {
		return excelScope;
	}

	
}
